Option Explicit
Option Compare Text
Sub Ecarts()
'Macro Dan pour S. cassandra - 30/06/17
'Mise _ jour feuilles EcartPP et feuille Ecart Indirects
Dim LigSDS As Byte, LigEpp As Byte, i As Byte
Dim noms()
Application.ScreenUpdating = False
noms = Array("MOD", "BETON", "ACIER", "AUTRES MATERIAUX")
For i = 0 To UBound(noms)
    With Sheets("Ecart PP")
        .Unprotect
        'On Error Resume Next
        LigSDS = Sheets("Synthese DS").Range("A13:A" & Sheets("Synthese DS").Range("A" & Sheets("Synthese DS").Rows.Count).End(xlUp).Row).Find(noms(i), LookIn:=xlValues, LookAt:=xlWhole).Row
        
        LigEpp = .Range("C:C").Find(noms(i), LookIn:=xlValues, LookAt:=xlWhole).Row
        If i < 3 Then
            .Range("H" & LigEpp + 3) = Sheets("Synthese DS").Range("I" & LigSDS)
            .Range("H" & LigEpp + 4) = Sheets("Synthese DS").Range("K" & LigSDS)
            .Range("J" & LigEpp + 3) = Sheets("Synthese DS").Range("J" & LigSDS) / Sheets("Synthese DS").Range("I" & LigSDS)
            .Range("J" & LigEpp + 4) = Sheets("Synthese DS").Range("L" & LigSDS) / Sheets("Synthese DS").Range("K" & LigSDS)
        Else: .Range("K" & LigEpp) = Sheets("Synthese DS").Range("N" & LigSDS) / 1000
        End If
    End With

Next i
Sheets("Ecart PP").Protect

'Mise a jour Feuille Ecart Indirects
Dim LigRI As Byte, j As Byte, dlgEI As Byte

With Sheets("Ecart Indirects")
    .Unprotect
    dlgEI = .Cells.Find("*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'derniere ligne feuille Ecarts indirect
    .Range("B21:O" & dlgEI + 100).Clear 'Efface la plage ligne 21 _ 100
End With

LigRI = Sheets("RECAP IND").Range("A" & Sheets("RECAP IND").Rows.Count).End(xlUp).Row 'derniere ligne feuille recap ind
j = 9 'definition interval entre deux donnees
For i = 12 To LigRI - 1
    If InStr(1, Sheets("RECAP IND").Range("A" & i), "MISSION", vbTextCompare) = 0 _
        And InStr(1, Sheets("RECAP IND").Range("A" & i), "RECETTE", vbTextCompare) = 0 _
        And InStr(1, Sheets("RECAP IND").Range("A" & i), "DEPENSE", vbTextCompare) = 0 Then
        
        With Sheets("Ecart Indirects")
            .Range("C" & i + j) = UCase(Sheets("RECAP IND").Range("A" & i))
            .Range("L" & i + j) = Round(Sheets("RECAP IND").Range("G" & i) / 1000, 0)
            .Range("L" & i + j).NumberFormat = "#,##0"
            .Range("M" & i + j) = Sheets("RECAP IND").Range("H" & i)
            .Range("M" & i + j).NumberFormat = "0.00%"
            With .Range("C" & i + j, .Range("M" & i + j))
                .Font.Name = "Book Antiqua"
                .Font.Size = 20
                .Borders(xlEdgeBottom).LineStyle = xlContinuous
                '.Borders(xlEdgeBottom).LineStyle = xlThin
            End With
            With .Range("L" & i + j, .Range("M" & i + j))
                .Font.Bold = True
                .HorizontalAlignment = xlRight
            End With
        End With
        j = j + 10
    End If
Next i
With Sheets("Ecart Indirects")
    dlgEI = .Cells.Find("*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'derniere ligne feuille Ecarts indirect
    .Range("B9:O" & dlgEI + 10).BorderAround LineStyle:=xlDouble, ColorIndex:=1, Weight:=xlThick
    .Protect
End With
Dim dlgEc As Byte
noms = Array("ECART STGO", "ECART STCEA", "ECART STCET")
For i = 0 To UBound(noms)
    With Sheets(noms(i))
        .Unprotect
        dlgEc = .Cells.Find("*", LookIn:=xlValues, SearchDirection:=xlPrevious).Row 'derniere ligne non vide apres ligne 19 - ligne ajoutee le 10/10/17 - Dan
       If dlgEc > 19 Then .Range("B20:O" & .Cells.Find("*", LookIn:=xlValues, SearchDirection:=xlPrevious).Row).ClearContents
        .Protect
    End With
Next
Application.ScreenUpdating = True
End Sub
